﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using uMES.LeanManufacturing.DBUtility;
using uMES.LeanManufacturing.ParameterDTO;

namespace uMES.LeanManufacturing.ReportBusiness
{
    public class uMESWorkReportBusiness
    {
        #region 更改报工记录IsChecked属性
        public Boolean UpdateReportIsChecked(string strID, int intIsChecked)
        {
            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine(string.Format("UPDATE workreportinfo SET ischecked = {1} WHERE workreportinfoid = '{0}'", strID, intIsChecked));

            OracleHelper.ExecuteSql(strSQL.ToString());

            return true;
        }
        #endregion

        #region 获取报工单的有效产品序号
        public DataTable GetWorkReportProductNo(string strWorkReportInfoID)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT rpi.containerid,rpi.containername,rpi.productno");
            strQuery.AppendLine("FROM reportproductnoinfo rpi");
            strQuery.AppendLine("LEFT JOIN workreportinfo wri ON wri.workreportinfoid = rpi.reportinfoid");
            strQuery.AppendLine("LEFT JOIN container c ON c.containerid = rpi.containerid");
            strQuery.AppendLine("WHERE 1 = 1");
            strQuery.AppendLine("AND c.status = 1");
            strQuery.AppendLine(string.Format("AND rpi.reportinfoid = '{0}'", strWorkReportInfoID));
            
            return OracleHelper.GetDataTable(strQuery.ToString());
        }
        #endregion

        #region 添加报工记录
        /// <summary>
        /// 添加报工记录
        /// </summary>
        /// <param name="para"></param>
        /// <returns></returns>
        public Boolean AddWorkReportInfo(Dictionary<string, string> para, DataTable dtProductNo, DataTable dtEmployee)
        {
            ArrayList SQLStringList = new ArrayList();

            //主表
            string strID = "";
            if (para.ContainsKey("WorkReportInfoID"))
                strID =para["WorkReportInfoID"];
            else
                strID = Guid.NewGuid().ToString();

            StringBuilder strSQL = new StringBuilder();
            strSQL.AppendLine("INSERT INTO workreportinfo(workreportinfoid,workreportinfoname,containerid,specid,qty,uomid,resourceid,reportemployeeid,reportdate,");
            strSQL.AppendLine("     reporttype,dispatchinfoid,notes,synergicinfoid,workflowid,mutualcheckerid,ischecked,FactoryID,workflowstepid)");
            strSQL.AppendLine("VALUES (");
            strSQL.AppendLine(string.Format("'{0}',", strID));
            strSQL.AppendLine(string.Format("'{0}',", para["WorkReportInfoName"]));
            strSQL.AppendLine(string.Format("'{0}',", para["ContainerID"]));
            strSQL.AppendLine(string.Format("'{0}',", para["SpecID"]));
            strSQL.AppendLine(string.Format("{0},", para["Qty"]));
            strSQL.AppendLine(string.Format("'{0}',", para["UOMID"]));
            strSQL.AppendLine(string.Format("'{0}',", para["ResourceID"]));
            strSQL.AppendLine(string.Format("'{0}',", para["ReportEmployeeID"]));
            strSQL.AppendLine(string.Format("TO_DATE('{0}','YYYY-MM-DD HH24:MI:SS'),", para["ReportDate"]));
            strSQL.AppendLine(string.Format("{0},", para["ReportType"]));
            strSQL.AppendLine(string.Format("'{0}',", para["DispatchInfoID"]));
            strSQL.AppendLine(string.Format("'{0}',", para["Notes"]));
            strSQL.AppendLine(string.Format("'{0}',", para["SynergicInfoID"]));
            strSQL.AppendLine(string.Format("'{0}',", para["WorkflowID"]));
            strSQL.AppendLine(string.Format("'{0}',", para["MutualCheckerID"]));
            strSQL.AppendLine(string.Format("{0},", 0));
            strSQL.AppendLine(string.Format("'{0}',", para["FactoryID"]));
            strSQL.AppendFormat("'{0}'",para["WorkflowStepID"]);//add:Wangjh 1024
            strSQL.AppendLine(")");

            SQLStringList.Add(strSQL.ToString());
            //OracleHelper.ExecuteSql(strSQL.ToString());

            //产品序号
            if (dtProductNo.Rows.Count > 0)
            {
                foreach (DataRow row in dtProductNo.Rows)
                {
                    strSQL = new StringBuilder();
                    strSQL.AppendLine("INSERT INTO reportproductnoinfo(ID,reportinfoid,containerid,containername,productno,notes)");
                    strSQL.AppendLine("VALUES(sys_guid(),");
                    strSQL.AppendLine(string.Format("'{0}',", strID));
                    strSQL.AppendLine(string.Format("'{0}',", row["ContainerID"]));
                    strSQL.AppendLine(string.Format("'{0}',", row["ContainerName"]));
                    strSQL.AppendLine(string.Format("'{0}',", row["ProductNo"]));
                    strSQL.AppendLine(string.Format("'{0}'", ""));
                    strSQL.AppendLine(")");

                    SQLStringList.Add(strSQL.ToString());
                    //OracleHelper.ExecuteSql(strSQL.ToString());
                }
            }

            //加工人员
            if (dtEmployee.Rows.Count > 0)
            {
                foreach (DataRow row in dtEmployee.Rows)
                {
                    strSQL = new StringBuilder();
                    strSQL.AppendLine("INSERT INTO reportemployeeinfo(ID,reportinfoid,employeeid,notes,workhours)");
                    strSQL.AppendLine("VALUES(sys_guid(),");
                    strSQL.AppendLine(string.Format("'{0}',", strID));
                    strSQL.AppendLine(string.Format("'{0}',", row["EmployeeID"]));
                    strSQL.AppendLine(string.Format("'{0}',", ""));
                    strSQL.AppendLine(string.Format("'{0}'", row["WorkHours"].ToString()));
                    strSQL.AppendLine(")");

                    SQLStringList.Add(strSQL.ToString());
                    //OracleHelper.ExecuteSql(strSQL.ToString());
                }
            }

            OracleHelper.ExecuteSqlTran(SQLStringList);

            return true;
        }
        #endregion

        #region 获取互检人
        public DataTable GetMutualChecker(string strFactoryID, string strEmployeeID, string strTeamID)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT DISTINCT e.employeeid,e.fullname");//,rd.rolename
            strQuery.AppendLine("FROM employeerole er");
            strQuery.AppendLine("LEFT JOIN employee e ON e.employeeid = er.employeeid");
            strQuery.AppendLine("LEFT JOIN sessionvalues sv ON sv.sessionvaluesid = e.sessionvaluesid");
            strQuery.AppendLine("LEFT JOIN roledef rd ON rd.roleid = er.roleid");
            strQuery.AppendLine("WHERE 1 = 1");
            //strQuery.AppendLine("AND rd.rolename = '互检人'");
            strQuery.AppendLine(string.Format("AND sv.factoryid = '{0}'", strFactoryID));
            strQuery.AppendLine(string.Format("AND e.teamid = '{0}'", strTeamID));
            strQuery.AppendLine(string.Format("AND e.employeeid <> '{0}'", strEmployeeID));

            return OracleHelper.GetDataTable(strQuery.ToString());
        }
        #endregion

        #region 获取班组派工记录未指派的产品序号
        public DataTable GetWillReportProductNo(string strDispatchInfoID)
        {
            DataTable dtProductNo = GetProductNoByDispatchID(strDispatchInfoID);
            DataTable dtDispatchedProductNo = GetReportedProductNo(strDispatchInfoID);

            DataTable DT = dtProductNo.Clone();
            foreach (DataRow row in dtProductNo.Rows)
            {
                string strContainerID = row["ContainerID"].ToString();

                Boolean isReported = false;
                foreach (DataRow r in dtDispatchedProductNo.Rows)
                {
                    string strID = r["ContainerID"].ToString();

                    if (strContainerID == strID)
                    {
                        isReported = true;
                        break;
                    }
                }

                if (isReported == false)
                {
                    DT.Rows.Add(row.ItemArray);
                }
            }

            return DT;
        }
        #endregion

        #region 获取派工记录的产品序号
        public DataTable GetProductNoByDispatchID(string strDispatchInfoID)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT dpi.containerid,dpi.containername,dpi.productno");
            strQuery.AppendLine("FROM dispatchproductnoinfo dpi");
            strQuery.AppendLine(string.Format("WHERE dpi.dispatchinfoid = '{0}'", strDispatchInfoID));

            return OracleHelper.GetDataTable(strQuery.ToString());
        }
        #endregion

        #region 获取派工记录已报工的产品序号
        public DataTable GetReportedProductNo(string strDispatchInfoID)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT rpi.containerid,rpi.containername,rpi.productno");
            strQuery.AppendLine("FROM reportproductnoinfo rpi");
            strQuery.AppendLine("LEFT JOIN workreportinfo wri ON wri.workreportinfoid = rpi.reportinfoid");
            strQuery.AppendLine(string.Format("WHERE wri.dispatchinfoid = '{0}'", strDispatchInfoID));

            return OracleHelper.GetDataTable(strQuery.ToString());
        }
        #endregion

        #region 获取派工记录已报工的数量
        public int GetReportedQty(string strDispatchInfoID)
        {
            string strQuery = string.Format("SELECT NVL(SUM(wri.qty),0) AS qty FROM workreportinfo wri WHERE wri.dispatchinfoid = '{0}'", strDispatchInfoID);

            DataTable Dt = OracleHelper.GetDataTable(strQuery);
            return Convert.ToInt32(Dt.Rows[0][0]);
        }
        #endregion

        #region 获取派工任务
        #region 分页查询
        public uMESPagingDataDTO GetSourceData(Dictionary<string, string> para, int intPageIndex, int intPageSize)
        {
            string strSQL = GetSQL_D(para);

            uMESPagingDataDTO retR = OracleHelper.GetPagingDataIns(strSQL, intPageIndex, intPageSize);
            return retR;
        }
        #endregion

        #region 不分页查询，用于导出
        public DataTable GetAllDataForOutExcel(Dictionary<string, string> para)
        {
            string strSQL = GetSQL_D(para);

            DataTable DT = OracleHelper.GetDataTable(strSQL);
            return DT;
        }
        #endregion

        #region 组合查询语句
        protected string GetSQL_D(Dictionary<string, string> para)
        {
            StringBuilder strQuery = new StringBuilder();
            strQuery.AppendLine("SELECT mo.processno,mo.oprno,c.containername,pb.productname,p.description,");
            strQuery.AppendLine("   di.qty,sb.specname,t.teamname,di.plannedcompletiondate,di.dispatchinfoname,");
            strQuery.AppendLine("   di.id,di.teamid,c.qty AS contaiernqty,c.containerid,di.workflowid,di.specid,di.parentid,");
            strQuery.AppendLine("   c.plannedstartdate AS containerstartdate,c.plannedcompletiondate AS containercompletiondate,");
            strQuery.AppendLine("   r.resourcename,di.resourceid,di1.qty AS parentqty,c.productid,mo.mfgordername,mo.mfgorderid,");
            strQuery.AppendLine("   s.unitworktime,s.setupworktime,c.qty * s.unitworktime AS totalworktime,di.workflowstepid");//add:Wangjh 1024 workflowstepid
            strQuery.AppendLine("FROM dispatchinfo di");
            strQuery.AppendLine("LEFT JOIN container c ON c.containerid = di.containerid");
            strQuery.AppendLine("LEFT JOIN mfgorder mo ON mo.mfgorderid = c.mfgorderid");
            strQuery.AppendLine("LEFT JOIN product p ON p.productid = c.productid");
            strQuery.AppendLine("LEFT JOIN productbase pb ON pb.productbaseid = p.productbaseid");
            strQuery.AppendLine("LEFT JOIN spec s ON s.specid = di.specid");
            strQuery.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = s.specbaseid");
            strQuery.AppendLine("LEFT JOIN team t ON t.teamid = di.teamid");
            strQuery.AppendLine("LEFT JOIN resourcedef r ON r.resourceid = di.resourceid");
            strQuery.AppendLine("LEFT JOIN dispatchinfo di1 ON di1.id = di.parentid");
            strQuery.AppendLine("WHERE c.status = 1");
            strQuery.AppendLine("AND c.parentcontainerid IS NULL");
            strQuery.AppendLine("AND di.specid = (");
            strQuery.AppendLine("SELECT NVL(sb.revofrcdid, wfs.specid) AS specid");
            strQuery.AppendLine("FROM currentstatus cu");
            strQuery.AppendLine("LEFT JOIN workflowstep wfs ON wfs.workflowstepid = cu.workflowstepid");
            strQuery.AppendLine("LEFT JOIN specbase sb ON sb.specbaseid = wfs.specbaseid");
            strQuery.AppendLine("WHERE cu.currentstatusid = c.currentstatusid");
            strQuery.AppendLine(")");
            strQuery.AppendLine("AND di.status in (20,25,30) "); //已接收未完成 update:Wangjh 更改为 in(20,30)
             //排查已进行外协申请的批次
            strQuery.AppendLine(@" and not EXISTS(
select 1 from synergicinfo st
where st.isuse=0 and st.containerid=di.containerid and st.workflowid=di.workflowid and di.specsequence between st.sequence and st.returnsequence
) ");

            if (para.Keys.Contains("ReceiveEmployeeID")&& !string.IsNullOrEmpty(para["ReceiveEmployeeID"]))//add:Wangjh 20201126
            {
                strQuery.AppendLine(string.Format(" AND di.receiveemployeeid = '{0}' ", para["ReceiveEmployeeID"]));
            }

            if (para.Keys.Contains("ReportEmployeeID") && !string.IsNullOrEmpty(para["ReportEmployeeID"]))//add:Wangjh 20210818，报工人可以为派工指定的人以内
            {
                strQuery.AppendFormat(" and exists (select 1 from dispatchemployeeinfo de where de.dispatchinfoid=di.id and de.employeeid='{0}') ", para["ReportEmployeeID"]);
            }

            if (para.Keys.Contains("DispatchType"))
            {
                if (!string.IsNullOrEmpty(para["DispatchType"]))
                {
                    strQuery.AppendLine(string.Format("AND di.dispatchtype = {0}", para["DispatchType"]));
                    //strQuery.AppendLine(string.Format("AND di.dispatchtotype = {0}", para["DispatchType"]));
                }
            }

            if (para.Keys.Contains("FactoryID"))
            {
                if (!string.IsNullOrEmpty(para["FactoryID"]))
                {
                    //strQuery.AppendLine(string.Format("AND c.containername = '{0}'", para["ScanContainerName"]));
                }
            }
            if (para.Keys.Contains("TeamID"))
            {
                if (!string.IsNullOrEmpty(para["TeamID"]))
                {
                    strQuery.AppendLine(string.Format("AND di.teamid = '{0}'", para["TeamID"]));
                }
            }
            if (para.Keys.Contains("Status"))
            {
                if (!string.IsNullOrEmpty(para["Status"]))
                {
                    strQuery.AppendLine(string.Format("AND di.status = {0}", para["Status"]));
                }
            }

            if (para.Keys.Contains("ScanContainerName")) //扫描的批次号
            {
                if (!string.IsNullOrEmpty(para["ScanContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND c.containername = '{0}'", para["ScanContainerName"]));
                }
            }
            if (para.Keys.Contains("ProcessNo")) //工作令号
            {
                if (!string.IsNullOrEmpty(para["ProcessNo"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(mo.processno) LIKE '%{0}%'", para["ProcessNo"].ToLower()));
                }
            }
            if (para.Keys.Contains("ContainerName")) //批次号
            {
                if (!string.IsNullOrEmpty(para["ContainerName"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(c.containername) LIKE '%{0}%'", para["ContainerName"].ToLower()));
                }
            }
            if (para.Keys.Contains("ProductName")) //图号/名称
            {
                if (!string.IsNullOrEmpty(para["ProductName"]))
                {
                    strQuery.AppendLine(string.Format("AND (LOWER(pb.productname) LIKE '%{0}%' OR LOWER(p.description) LIKE '%{0}%')", para["ProductName"].ToLower()));
                }
            }
            if (para.Keys.Contains("SpecName")) //工序
            {
                if (!string.IsNullOrEmpty(para["SpecName"]))
                {
                    strQuery.AppendLine(string.Format("AND LOWER(sb.specname) LIKE '%{0}%'", para["SpecName"].ToLower()));
                }
            }
            if (para.Keys.Contains("StartDate")) //开始时间
            {
                if (!string.IsNullOrEmpty(para["StartDate"]))
                {
                    strQuery.AppendLine(string.Format("AND di.plannedcompletiondate >= TO_DATE('{0} 00:00:00','yyyy-MM-dd HH24:MI:SS')", para["StartDate"]));
                }
            }
            if (para.Keys.Contains("EndDate")) //结束时间
            {
                if (!string.IsNullOrEmpty(para["EndDate"]))
                {
                    strQuery.AppendLine(string.Format("AND di.plannedcompletiondate <= TO_DATE('{0} 23:59:59','yyyy-MM-dd HH24:MI:SS')", para["EndDate"]));
                }
            }

            strQuery.AppendLine("ORDER BY di.dispatchdate DESC");

            return strQuery.ToString();
        }
        #endregion
        #endregion
    }
}
